package main

import (
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"github.com/go-xorm/xorm"
	"math/rand"
	"strconv"
	"time"
	"xorm.io/core"
)

type TbGroupInfo struct {
	CreateId    string    `xorm:"comment('创建人') VARCHAR(32)"`
	CreateTime  time.Time `xorm:"comment('创建时间') DATETIME"`
	DelFlag     int       `xorm:"not null default 0 comment('是否删除 0 未删除 ,1 已删除 ') TINYINT(1)"`
	EnableFlag  int       `xorm:"not null default 0 comment('是否可用 0 可用 ,1 不可用 ') TINYINT(1)"`
	GroupId     int       `xorm:"not null comment('群组编号') INT"`
	GroupLabel  string    `xorm:"default '' comment('群组标签') VARCHAR(255)"`
	GroupLevel  int       `xorm:"not null comment('群组级别') INT"`
	GroupName   string    `xorm:"default '' comment('群组名称') VARCHAR(64)"`
	GroupPublic string    `xorm:"default '' comment('群组公告') VARCHAR(4000)"`
	GroupRemark string    `xorm:"default '' comment('群组备注') VARCHAR(255)"`
	IconUrl     string    `xorm:"default '' comment('群组图标') VARCHAR(255)"`
	Id          int       `xorm:"not null pk autoincr comment('主键ID') INT"`
	PlatId      int       `xorm:"not null comment('平台ID') INT"`
	Status      int       `xorm:"comment('群组状态') TINYINT(1)"`
	UpdateId    string    `xorm:"comment('修改人') VARCHAR(32)"`
	UpdateTime  time.Time `xorm:"comment('修改时间') DATETIME"`
}

type TbUserInfo struct {
	CreateId     string    `xorm:"comment('创建人') VARCHAR(32)"`
	CreateTime   time.Time `xorm:"comment('创建时间') DATETIME"`
	DelFlag      int       `xorm:"not null default 0 comment('是否删除 0 未删除 ,1 已删除 ') TINYINT(1)"`
	EnableFlag   int       `xorm:"not null default 0 comment('是否可用 0 可用 ,1 不可用 ') TINYINT(1)"`
	IconUrl      string    `xorm:"not null default '0' comment('第三方用户头像') VARCHAR(255)"`
	Id           int       `xorm:"not null pk autoincr comment('主键ID') INT"`
	IdentityCode string    `xorm:"default '' comment('身份编码') VARCHAR(64)"`
	NickName     string    `xorm:"default '' comment('昵称') VARCHAR(64)"`
	ParentId     string    `xorm:"default '' comment('第三方用户ID上级ID') index(IDX_PLAT_USER_4) VARCHAR(32)"`
	ParentIds    string    `xorm:"default '' comment('第三方用户所有上级ID') VARCHAR(255)"`
	PlatId       int       `xorm:"not null comment('平台ID') index index(IDX_PLAT_USER_2) index(IDX_PLAT_USER_3) index(IDX_PLAT_USER_4) INT"`
	ThirdId      string    `xorm:"not null comment('第三方用户ID,用于做唯一标识') index(IDX_PLAT_USER_3) VARCHAR(64)"`
	UpdateId     string    `xorm:"comment('修改人') VARCHAR(32)"`
	UpdateTime   time.Time `xorm:"comment('修改时间') DATETIME"`
	UserId       int       `xorm:"not null comment('授权用户ID') index(IDX_PLAT_USER_2) INT"`
	UserName     string    `xorm:"comment('三方账号') VARCHAR(64)"`
	VipCode      string    `xorm:"default '' comment('vip编码') VARCHAR(64)"`
}

type TLiveStateTimes struct {
	Id          int `xorm:"not null pk autoincr comment('主键ID') INT"`
	PlatId      int `xorm:"not null comment('平台ID') index(t_live_state_times_1) INT"`
	UserId      int `xorm:"not null comment('用户ID') index(t_live_state_times_1) INT"`
	State       int `xorm:"not null comment('用户状态（0，在线 1，忙碌 2，离线）') index(t_live_state_times_1) INT"`
	BetweenTime int `xorm:"comment('状态持续时间') INT"`
	EnableFlag  int `xorm:"default 0 comment('是否可用 0 可用 ,1 不可用 ') TINYINT(1)"`
	DelFlag     int `xorm:"default 0 comment('是否删除 0 未删除 ,1 已删除') TINYINT(1)"`
	Date        int `xorm:"not null comment('状态记录日期') index(t_live_state_times_1) INT"`
	StartTime   int `xorm:"not null comment('状态开始时间') INT"`
	EndTime     int `xorm:"comment('状态结束时间') INT"`
}

func main() {
	engine, err := xorm.NewEngine("mysql", "root:123456@(192.168.16.117:3306)/db_test?charset=utf8")

	if err != nil {
		fmt.Println(err)
		return
	}
	if err := engine.Ping(); err != nil {
		fmt.Println(err)
		return
	}
	// 设置连接池的空闲数大小
	engine.SetMaxIdleConns(5)
	// 设置最大打开连接数
	engine.SetMaxOpenConns(5)
	//// 名称映射规则主要负责结构体名称到表名和结构体field到表字段的名称映射
	//engine.SetColumnMapper(core.SameMapper{})
	// 则会在控制台打印出生成的SQL语句
	engine.ShowSQL(true)
	// 则会在控制台打印调试及以上的信息
	engine.Logger().SetLevel(core.LOG_DEBUG)

	/*// 增加数据
	user := new(User)
	user.Username = "tyming"
	affected, err := engine.Insert(user)
	fmt.Println(affected, err)

	//删除数据
	user1 := new(User)
	user1.Username = "tyming"
	affected_delete, err := engine.Delete(user1)
	fmt.Println(affected_delete)

	//修改数据
	user2 := new(User)
	user2.Username = "tyming"
	affected_update, err := engine.Id(1).Update(user2)
	fmt.Println(affected_update)

	// 查询
	user3 := new(User)
	result, err := engine.Id(1).Get(user3)
	//retult, err := engine.Where("id=?", 1).Get(user3)
	fmt.Println(result)*/

	TestDemo(engine)
}

func TestDemo(dB *xorm.Engine) {
	fmt.Println("测试mysql")

	// 插入数据
	// InsertTestUserData(dB)
	//InsertTestGroupData(dB)

	// SearchTestData(dB)

	SqlTest(dB)

}

func TimeStampToDate(timeStamp int) time.Time {
	tm2 := time.Unix(int64(timeStamp), 0)
	return tm2
}

func GetTestUserName() string {
	str := "ABCDEFGHIJKLMNOPQRSTUVWXYZZ"
	rand.Seed(time.Now().UnixNano())
	num := "01234567899"

	str1 := rand.Intn(26)
	str2 := rand.Intn(26)
	str3 := rand.Intn(26)
	str4 := rand.Intn(26)
	str5 := rand.Intn(26)
	str6 := rand.Intn(26)

	num1 := rand.Intn(10)
	num2 := rand.Intn(10)
	num3 := rand.Intn(10)
	num4 := rand.Intn(10)

	return "AB" + str[str1:str1+1] + str[str2:str2+1] + str[str3:str3+1] + str[str4:str4+1] + str[str5:str5+1] +
		str[str6:str6+1] + num[num1:num1+1] + num[num2:num2+1] + num[num3:num3+1] + num[num4:num4+1]
}

func InsertTestUserData(dB *xorm.Engine) {
	fmt.Println("插入用户测试数据")

	i := 2000000
	for {
		if i >= 5000000 {
			break
		}
		i++
		rand.Seed(time.Now().UnixNano())
		userInfo := new(TbUserInfo)
		userInfo.Id = i
		userInfo.UserId = rand.Intn(1000000000) + 2000000011
		userInfo.PlatId = rand.Intn(500) + 200055
		userInfo.IconUrl = "11/" + strconv.Itoa(rand.Intn(1000000000)) + "d01c3ac0dfdc005dfaaf.jpeg"
		userInfo.CreateId = strconv.Itoa(rand.Intn(100) + 2000000011)
		userInfo.CreateTime = TimeStampToDate(rand.Intn(31622399) + 1577808000)
		userInfo.ParentId = strconv.Itoa(rand.Intn(40) + 500001)
		userInfo.ThirdId = strconv.Itoa(rand.Intn(400) + 500001)
		userInfo.UpdateId = userInfo.CreateId
		userInfo.UserName = GetTestUserName()
		userInfo.NickName = userInfo.UserName
		_, err := dB.Insert(userInfo)
		if err != nil {
			fmt.Println("插入数据错误", err)
		}

	}
}

func InsertTestGroupData(dB *xorm.Engine) {
	fmt.Println("插入群组测试数据")

	i := 53000
	for {
		if i >= 54000 {
			break
		}
		i++
		data := new(TbGroupInfo)
		data.Id = i
		data.CreateId = strconv.Itoa(rand.Intn(100) + 2000000011)
		data.UpdateId = data.CreateId
		// data.CreateTime = TimeStampToDate(rand.Intn(31622399) + 1577808000)
		data.IconUrl = "12/" + strconv.Itoa(rand.Intn(1000000000)) + "d01c3ac0dfdc005dfaaf.jpeg"
		data.PlatId = rand.Intn(500) + 200055
		data.Status = rand.Intn(1)
		data.GroupId = rand.Intn(10000000) + 20000011
		data.GroupLevel = rand.Intn(100) + 100001
		data.GroupName = GetTestUserName()
		// data.UpdateTime = data.CreateTime
		data.GroupRemark = GetTestUserName()

		_, err := dB.Insert(data)
		if err != nil {
			fmt.Println("插入数据错误", err)
		}
	}

}

func SearchTestData(dB *xorm.Engine) {
	data := new(TLiveStateTimes)
	_, err := dB.Where("plat_id=?", 190051).
		And("user_id=?", 164).
		OrderBy("start_time desc").
		Limit(1, 0).
		Get(data)
	if err != nil {
		fmt.Println("查询数据错误", err)
	} else {
		fmt.Println("获取数据成功：", data)
	}
	newData := TLiveStateTimes{
		EndTime:     1607940205,
		BetweenTime: 0,
	}
	dB.Where("id=?", data.Id).Update(&newData)

}

func SqlTest(dB *xorm.Engine) {
	/*dB.SQL("")
	dB.QueryString("")
	dB.Query("")
	dB.Exec("")*/

	/* SQL 适合查询 */
	// 查询一个
	//data := new(TbGroupInfo)
	//sql := fmt.Sprintf("SELECT * FROM `db_test`.`tb_group_info` WHERE `group_id` = '%d' LIMIT 0,10", 23161218)
	//_, err := dB.SQL(sql).Get(data)
	//if err != nil{
	//	fmt.Println("查询错误：", err)
	//	return
	//}
	//fmt.Println("data:", data)

	//
	//// 查询多个
	data1 := make([]*TbGroupInfo, 0)
	err := dB.SQL("SELECT * FROM `db_test`.`tb_group_info` WHERE `plat_id`=?  LIMIT 0,1000", 200091).Find(&data1)
	if err != nil {
		fmt.Println("查询错误：", err)
		return
	}
	fmt.Println("data1:", data1)

	/* QueryString 适合查询结果为map, 更新操作 */
	//// 查询一个
	//sql := fmt.Sprintf("SELECT * FROM `db_test`.`tb_group_info` WHERE `group_id` = '%d' LIMIT 0, 10", 23161218)
	//RlsMap, err := dB.QueryString(sql)
	//if err != nil{
	//	fmt.Println("查询错误：", err)
	//	return
	//}
	//fmt.Println("RlsMap:", RlsMap)
	//

	//// 查询多个
	//RlsMap1, err := dB.QueryString("SELECT * FROM `db_test`.`tb_group_info` WHERE `plat_id` = '200091' LIMIT 0,1000" )
	//if err != nil{
	//	fmt.Println("查询错误：", err)
	//	return
	//}
	//fmt.Println("RlsMap1:", RlsMap1)
	// 更新操作
	//sql := fmt.Sprintf("UPDATE tb_group_info SET `group_remark` = '%s'  WHERE group_id = '%d'", "hshshshshsh" ,26663269)
	//RlsMap, err := dB.QueryString(sql)
	//if err != nil{
	//	fmt.Println("更新错误：", err)
	//	return
	//}
	//fmt.Println("RlsMap:", RlsMap)

	/* Exec 适合插入操作*/
	//// 更新操作
	//sql := fmt.Sprintf("UPDATE tb_group_info SET `group_remark` = '%s'  WHERE group_id = '%d'", "haahahah" ,26663269)
	//_, err := dB.Exec(sql)
	//if err != nil{
	//	fmt.Println("查询错误：", err)
	//	return
	//}

	/* 查询结果为map，value为[]byte类型*/
	//sql := fmt.Sprintf("SELECT * FROM `db_test`.`tb_group_info` WHERE `group_id` = '%d' LIMIT 0, 10", 23161218)
	//RlsMap, err := dB.Query(sql)
	//if err != nil{
	//	fmt.Println("查询错误：", err)
	//	return
	//}
	//fmt.Println("RlsMap:", RlsMap)

	//sql := fmt.Sprintf("SELECT * FROM `db_test`.`tb_group_info` WHERE `group_id` = '%d' LIMIT 0, 10", 23161218)
	//RlsMap, err := dB.QueryInterface(sql)

}
